using System;
using System.Web;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using CE.Configuration;
using CE.InputOut;

/// <summary>
/// Summary description for MyDatabase
/// Database Function(Update, Delete, Insert, Select) for all class
/// 
/// </summary>
/// 
namespace CE.Database
{
    public class MyDatabase
    {
        private string _connectionString;

        public MyDatabase()
        {
            _connectionString = MyConfigurationManager.GetConnectionString("CX_CRMDBConnectionString");
        }

        public MyDatabase(string ConnectionStringName)
        {
            _connectionString = MyConfigurationManager.GetConnectionString(ConnectionStringName);
        }

        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        public SqlDataReader GetSqlDataReader(string sqlStatment)
        {
            SqlDataReader _sqlreader = null;
            SqlCommand _sqlcmd = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcon.Open();
                _sqlcmd = new SqlCommand(sqlStatment, _sqlcon);
                _sqlcmd.CommandType = CommandType.Text;
                _sqlreader = _sqlcmd.ExecuteReader();
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "GetSqlDataReader(string sqlStatment)", sqlStatment);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "GetSqlDataReader(string sqlStatment)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }

            return _sqlreader;
        }

        public SqlDataReader GetSqlDataReader(string sqlStatment, SortedList sqlPara)
        {
            SqlDataReader _sqlreader = null;
            SqlCommand _sqlcmd = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand(sqlStatment, _sqlcon);
                _sqlcmd.CommandType = CommandType.Text;

                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }
                _sqlcon.Open();
                _sqlreader = _sqlcmd.ExecuteReader();
                _sqlcon.Close();
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "GetSqlDataReader(string sqlStatment, SortedList sqlPara)", sqlStatment);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "GetSqlDataReader(string sqlStatment, SortedList sqlPara)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }

            return _sqlreader;
        }

        public DataTable GetDataTable(string sqlStatement, SortedList sqlPara)
        {
            DataTable _datatable = null;
            SqlCommand _sqlcmd = null;
            SqlDataAdapter _sqlda = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcon.Open();
                _sqlcmd = new SqlCommand(sqlStatement, _sqlcon);
                _sqlcmd.CommandType = CommandType.Text;
                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }
                _sqlda = new SqlDataAdapter(_sqlcmd);
                _datatable = new DataTable();
                _sqlda.Fill(_datatable);
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "GetDataTable(string sqlStatement, SortedList sqlPara)", sqlStatement);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "GetDataTable(string sqlStatement, SortedList sqlPara)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlda != null)
                {
                    _sqlda = null;
                }

                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }

            return _datatable;
        }

        public DataTable GetDataTableBySP(string spName, SortedList sqlPara)
        {
            DataTable _datatable = null;
            SqlCommand _sqlcmd = null;
            SqlDataAdapter _sqlda = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcon.Open();
                _sqlcmd = new SqlCommand(spName, _sqlcon);
                _sqlcmd.CommandType = CommandType.StoredProcedure;
                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }
                _sqlda = new SqlDataAdapter(_sqlcmd);
                _datatable = new DataTable();
                _sqlda.Fill(_datatable);
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "GetDataTableBySP(string spName, SortedList sqlPara)", spName);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "GetDataTableBySP(string spName, SortedList sqlPara)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlda != null)
                {
                    _sqlda = null;
                }
                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }

            return _datatable;
        }

        public DataTable GetDataTableBySP(string spName)
        {
            DataTable _datatable = null;
            SqlCommand _sqlcmd = null;
            SqlDataAdapter _sqlda = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcon.Open();
                _sqlcmd = new SqlCommand(spName, _sqlcon);
                _sqlcmd.CommandType = CommandType.StoredProcedure;
                 _sqlda = new SqlDataAdapter(_sqlcmd);
                _datatable = new DataTable();
                _sqlda.Fill(_datatable);
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "GetDataTableBySP(string spName)", spName);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "GetDataTableBySP(string spName)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlda != null)
                {
                    _sqlda = null;
                }
                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }

            return _datatable;
        }

        public DataTable GetDataTable(string sqlStatement)
        {
            DataTable _datatable = null;
            SqlCommand _sqlcmd = null;
            SqlDataAdapter _sqlda = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcon.Open();
                _sqlcmd = new SqlCommand(sqlStatement, _sqlcon);
                _sqlcmd.CommandType = CommandType.Text;
                _sqlda = new SqlDataAdapter(_sqlcmd);
                _datatable = new DataTable();
                _sqlda.Fill(_datatable);
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "GetDataTable(string sqlStatement)", sqlStatement);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "GetDataTable(string sqlStatement)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlda != null)
                {
                    _sqlda = null;
                }
                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }

            return _datatable;
        }

        public DataTable GetDataTableXml(string xmlFilePath)
        {
            DataTable _datatable = null;

            try
            {
                _datatable.ReadXml(xmlFilePath);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "GetDataTableXml(string xmlFilePath)");
            }
            finally
            {

            }

            return _datatable;
        }

        public DataSet GetDataSet(string sqlStatment)
        {
            DataSet _dataset = null;
            SqlCommand _sqlcmd = null;
            SqlDataAdapter _sqlda = null;
            SqlConnection _sqlcon = null;
            try
            {
                _dataset = new DataSet();
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand(sqlStatment, _sqlcon);
                _sqlcmd.CommandType = CommandType.Text;
                _sqlda = new SqlDataAdapter(_sqlcmd);
                _sqlda.Fill(_dataset);
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "GetDataSet(string sqlStatment)", sqlStatment);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "GetDataSet(string sqlStatment)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlda != null)
                {
                    _sqlda = null;
                }
                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }
            return _dataset;
        }

        public DataSet GetDataSet(string spName, SortedList sqlPara)
        {
            DataSet _dataset = null;
            SqlCommand _sqlcmd = null;
            SqlDataAdapter _sqlda = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand(spName, _sqlcon);
                _sqlcmd.CommandType = CommandType.StoredProcedure;
                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }
                _sqlda = new SqlDataAdapter(_sqlcmd);
                _dataset = new DataSet();
                _sqlda.Fill(_dataset);
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "GetDataSet(string spName, SortedList sqlPara)", spName);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "GetDataSet(string spName, SortedList sqlPara)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlda != null)
                {
                    _sqlda = null;
                }
                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }
            return _dataset;
        }

        public int ExecuteNonSqlStatement(string sqlStatement)
        {
            int _affCount = -1;
            SqlCommand _sqlcmd = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcon.Open();
                _sqlcmd = new SqlCommand(sqlStatement, _sqlcon);
                _sqlcmd.CommandType = CommandType.Text;
                _affCount = _sqlcmd.ExecuteNonQuery();
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "ExecuteNonSqlStatement(string sqlStatement)", sqlStatement);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "ExecuteNonSqlStatement(string sqlStatement)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }
            return _affCount;
        }

        public int ExecuteNonSqlStatement(string sqlStatement, SortedList sqlPara)
        {
            int _affCount = -1;
            SqlCommand _sqlcmd = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);

                _sqlcmd = new SqlCommand(sqlStatement, _sqlcon);
                _sqlcmd.CommandType = CommandType.Text;

                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }

                _sqlcon.Open();
                _affCount = _sqlcmd.ExecuteNonQuery();
                _sqlcon.Close();

            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "ExecuteNonSqlStatement(string sqlStatement, SortedList sqlPara)", sqlStatement);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "ExecuteNonSqlStatement(string sqlStatement, SortedList sqlPara)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }
            return _affCount;
        }

        public Int64 ExecuteSP(string spName, SortedList sqlPara)
        {
            Int64 _result = -1;
            SqlCommand _sqlcmd = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);

                _sqlcmd = new SqlCommand();
                _sqlcmd.CommandType = CommandType.StoredProcedure;
                _sqlcmd.CommandText = spName;
                _sqlcmd.Connection = _sqlcon;

                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }

                DbParameter myReturnValue = null;
                myReturnValue = _sqlcmd.CreateParameter();
                myReturnValue.Direction = ParameterDirection.ReturnValue;
                _sqlcmd.Parameters.Add(myReturnValue);

                _sqlcon.Open();
                _sqlcmd.ExecuteNonQuery();
                _result = Convert.ToInt64(myReturnValue.Value);
                _sqlcon.Close();

                return _result;
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "ExecuteSP(string spName, SortedList sqlPara)", spName);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "ExecuteSP(string spName, SortedList sqlPara)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }
            return _result;
        }

        public int ExecuteNonSqlSP(string spName, SortedList sqlPara)
        {
            int _affCount = -1;
            SqlCommand _sqlcmd = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand(spName, _sqlcon);
                _sqlcmd.CommandType = CommandType.StoredProcedure;

                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }

                _sqlcon.Open();
                _affCount = _sqlcmd.ExecuteNonQuery();
                _sqlcon.Close();

                return _affCount;
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "ExecuteNonSqlSP(string spName, SortedList sqlPara)", spName);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "ExecuteNonSqlSP(string spName, SortedList sqlPara)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }
            return _affCount;
        }

        public object ExecuteScalarSP(string spName, SortedList sqlPara)
        {
            object _object = null;
            SqlCommand _sqlcmd = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcon.Open();
                _sqlcmd = new SqlCommand(spName, _sqlcon);
                _sqlcmd.CommandType = CommandType.StoredProcedure;

                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }

                _object = _sqlcmd.ExecuteScalar();
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "ExecuteScalarSP(string spName, SortedList sqlPara)", spName);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "ExecuteScalarSP(string spName, SortedList sqlPara)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }
            return _object;
        }

        public object ExecuteScalarSqlStatement(string sqlStatement, SortedList sqlPara)
        {
            object _object = null;
            SqlCommand _sqlcmd = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcon.Open();
                _sqlcmd = new SqlCommand(sqlStatement, _sqlcon);
                _sqlcmd.CommandType = CommandType.Text;

                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }

                _object = _sqlcmd.ExecuteScalar();
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "ExecuteScalarSqlStatement(string sqlStatement, SortedList sqlPara)", sqlStatement);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "ExecuteScalarSqlStatement(string sqlStatement, SortedList sqlPara)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }
            return _object;
        }

        public object ExecuteScalarSqlStatement(string sqlStatement)
        {
            object _object = null;
            SqlCommand _sqlcmd = null;
            SqlConnection _sqlcon = null;
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcon.Open();
                _sqlcmd = new SqlCommand(sqlStatement, _sqlcon);
                _sqlcmd.CommandType = CommandType.Text;

                _object = _sqlcmd.ExecuteScalar();
            }
            catch (SqlException sqlEx)
            {
                CreateSqlExceptionLog(sqlEx, "ExecuteScalarSqlStatement(string sqlStatement)", sqlStatement);
            }
            catch (Exception Ex)
            {
                CreateExceptionLog(Ex, "ExecuteScalarSqlStatement(string sqlStatement)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlcon.State != ConnectionState.Closed)
                {
                    _sqlcon.Close();
                    _sqlcon = null;
                }
            }
            return _object;
        }

        private void CreateSqlExceptionLog(SqlException sqlEx, string strLocation, string sqlStatment)
        {
            String[] _fileContent = new String[7];
            _fileContent[0] = "Source : " + sqlEx.Source;
            _fileContent[1] = "Date Time : " + DateTime.Now.ToString();
            _fileContent[2] = "Error Code :" + sqlEx.ErrorCode.ToString();
            _fileContent[3] = "Target Site : " + sqlEx.TargetSite.ToString();
            _fileContent[4] = "Location : " + "CE.Database.MyDatabase - " + strLocation;
            _fileContent[5] = "Message : " + sqlEx.Message;
            _fileContent[6] = "Sql Statment : " + sqlStatment;
            WriteExceptionLog("sqlEx_", _fileContent);
        }

        private void CreateExceptionLog(Exception Ex, string strLocation)
        {
            String[] _fileContent = new String[6];
            _fileContent[0] = "Source : " + Ex.Source;
            _fileContent[1] = "Date Time : " + DateTime.Now.ToString();
            _fileContent[2] = "Stack Trace :" + Ex.StackTrace.ToString();
            _fileContent[3] = "Target Site : " + Ex.TargetSite.ToString();
            _fileContent[4] = "Location : " + "CE.Database.MyDatabase - " + strLocation;
            _fileContent[5] = "Message : " + Ex.Message;

            WriteExceptionLog("Ex_", _fileContent);
        }

        public void CreateUnhandleLog(string strError)
        {
            String[] _fileContent = new String[2];
            _fileContent[0] = "Date Time : " + DateTime.Now.ToString();
            _fileContent[1] = "Message :" + strError;

            WriteExceptionLog("Unhandle_", _fileContent);
        }

        private void WriteExceptionLog(string strPrefixFileName, string[] strException)
        {
            try
            {
                MyIO _newFile = new MyIO();
                string _filePath = AppDomain.CurrentDomain.BaseDirectory + "Exception\\";
                if (_newFile.CheckDirectory(_filePath))
                {
                    string _curMonth = DateTime.Now.Year.ToString("0000") + DateTime.Now.Month.ToString("00");

                    if (!_newFile.CheckDirectory(_filePath + _curMonth))
                    {
                        if (_newFile.CreateDirectory(_filePath, _curMonth))
                        {
                            _filePath += _curMonth + "\\";
                        }
                    }
                    else
                    {
                        _filePath += _curMonth + "\\";
                    }

                    string _fileName = strPrefixFileName + _curMonth + DateTime.Now.Day.ToString("00") + DateTime.Now.Second.ToString("00") + DateTime.Now.Millisecond.ToString("00") + ".txt";

                    _newFile.WriteNewFile(_filePath, _fileName, strException);
                    _newFile = null;
                }
            }
            catch (Exception Ex)
            {
                Ex = null;
            }
        }
    }
}